﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;

namespace WFcontrol
{
    class accessdata
    {

      private SqlConnection con = new SqlConnection();
      //public SqlConnection getconn  //tao thuoc tinh truy cap
      //{
      //    get
      //    {
      //        return con;
      //    }
      //}
        public accessdata()
      { //sai:Data Source=.\SQLEXPRESS;AttachDbFilename="D:\Hoc tap cua tuan\c#\shopcd_Data.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True
            //return new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename="D:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.SQLEXPRESS\\MSSQL\DATA\\shopcd_Data.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True");
            //con.ConnectionString = "Data Source=PC201103271718\\SQLEXPRESS;Initial Catalog=shopcd;Integrated Security=True";    
            con.ConnectionString = "Data Source=.;Initial Catalog=shopcd;Integrated Security=True";
        }
        //public bool CheckLogin(string name, string pass)
        //{
        //    //SqlConnection con = getconn();
        //    SqlCommand cmd = new SqlCommand("select * from login where username='" + name + "' and password='" + pass + "'", con);
        //    con.Open();
        //    SqlDataReader dr = cmd.ExecuteReader();
        //    if (dr.Read())
        //    {
        //        con.Close();
        //        return true;
        //    }
        //    con.Close();
        //    return false;
        //}

     
       /* public bool checklogin(string name,string pass) {
          SqlConnection con = new SqlConnection();
          SqlCommand cmd = new SqlCommand("select * from login where userid='" + name + "' and pass = '" + pass + "'", con);
          con.Open();
          SqlDataReader dr = cmd.ExecuteReader();
          if (dr.Read()) {
              con.Close();
              return true;
          }
          con.Close();
          return false;
        }*/
        public void Open()
        {
            if (con.State == ConnectionState.Closed)
                con.Open();
        }
        public void Close()
        {
            if(con.State==ConnectionState.Open)
                con.Close();
        }
        public DataTable Get_DataTable(string SQL)
        {
            SqlDataAdapter adp = new SqlDataAdapter(SQL,con);
            DataTable tb= new DataTable();
            adp.Fill(tb);
            return tb;
        
        }
        public DataSet Get_DataSet(ref DataSet ds, String SQL)
        {
            DataTable tb=Get_DataTable(SQL);
             //   DataSet ds=new DataSet();
            ds.Tables.Add(tb);
            return ds;
        }
        public void Execute_SQL(string SQL)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = SQL;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        public DataTable Execute_ps(string name_ps,SqlParameter [] pr) {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText=name_ps;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
            cmd.Parameters.AddRange(pr);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable tb = new DataTable();
            adp.Fill(tb);
            return tb;
        }
        /*  
         ham thu tuc de lap 3 layer
                    con.Close();
   public DataTable Execute_Ps(string Name_ps, string MaDM, string TenDM)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = Name_ps;
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("@MaDM",MaDM);
            SqlParameter p2 = new SqlParameter("@TenDM",TenDM);
           // p1.SourceColumn = "@MaDM";//ten doi
           // p1.Value = MaDM;//giatri
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            //cmd.ExecuteNonQuery();
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable tb = new DataTable();
            adp.Fill(tb);
            return tb;

}
                 
         */
      /*  public DataTable Execute_Ps(string Name_ps, string MaDM, string TenDM)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = Name_ps;
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("@MaDM", MaDM);
            SqlParameter p2 = new SqlParameter("@TenDM", TenDM);
            // p1.SourceColumn = "@MaDM";//ten doi
            // p1.Value = MaDM;//giatri
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            //cmd.ExecuteNonQuery();
            SqlDataAdapter adp = new SqlDataAdapter(cmd);
            DataTable tb = new DataTable();
            adp.Fill(tb);
            return tb;

        }
        public void Update_DataSet(ref DataTable tb, String SQL)
        {
            SqlDataAdapter adp = new SqlDataAdapter(SQL,con);
            SqlCommandBuilder bd = new SqlCommandBuilder();
            bd.DataAdapter = adp;
            adp.Update(tb);
        }*/
    }
}
